#!/usr/bin/env python3
# -*- encoding: utf-8 -*-
'''
@File    :   db_operator.py
@Time    :   2023/04/03 22:10:39
@Author  :   youle
@Email   :   a_fly0505@163.com
@Copyright : 侵权必究
'''

from __future__ import annotations
import logging
import  model.db_models as dbModel
from model.db_models import Base, Engine, MarketStock, MarketFunc, StockQfq, FundQfq
import sqlalchemy
from sqlalchemy.orm import sessionmaker 
from sqlalchemy import Table, values, column
from sqlalchemy import select, delete, update, insert, inspect
import pandas as pd
import datetime

_create_fund_table = '''CREATE TABLE IF NOT EXISTS fund.fund_{}_{}
(
    id SERIAL PRIMARY KEY NOT NULL,
    date date NOT NULL UNIQUE,
    open real NOT NULL,
    close real NOT NULL,
    hight real NOT NULL,
    low real NOT NULL,
    volume double precision DEFAULT 0.0,
    volume_quota double precision DEFAULT 0.0,
    vibration real,
    rise_fall_rate real DEFAULT 0.0,
    rise_fall_quota real DEFAULT 0.0,
    turnover_rate real
);'''

_drop_fund_table = '''drop table if exists fund.fund_{}_{};'''

_insert_fund_table = '''INSERT INTO fund.fund_qfq_{}(
	date, open, close, hight, low, volume, volume_quota, vibration, rise_fall_rate, rise_fall_quota, turnover_rate)
	VALUES ('{}', {}, {}, {}, {}, {}, {}, {}, {}, {}, {});'''

_select_fund_sql = '''SELECT id,date,open,close,hight,low FROM fund.fund_{}_{} ORDER BY date DESC LIMIT {};'''


def CreateAllTable():
    Base.metadata.create_all(dbModel.Engine)



class StockOperator:
    def __init__(self) -> None:
        pass


class MarketFundOperator:
    def __init__(self) -> None:
        self.session = None
        # 保存基金key=code, value=table的map
        self._fund_table_map = {}

    def GetFundTable(self, code: str):
        fund_table = self._fund_table_map.get(code, None)
        if fund_table == None:
            fund_table = dbModel.make_fund("fund_{}_{}".format("qfq", code))
            self._fund_table_map[code] = fund_table
        return fund_table

    def Insert(self, items:list[MarketFunc]) ->bool:
        self.session = sessionmaker(dbModel.Engine)()
        for item in items:
            self.session.merge(item)
        self.session.commit()
        self.session.close()
        return True

    def QueryAll(self) ->list[MarketFunc]:
        self.session = sessionmaker(dbModel.Engine)()
        funds = self.session.query(MarketFunc).all()
        self.session.close()
        return funds


class FundOperator:
    def __init__(self) -> None:
        self.session = None

    def CreateAllTables(self, codes: list[str]) -> bool:
        self.session = sessionmaker(dbModel.Engine)()
        for code in codes:
            exec_sql = _create_fund_table.format("qfq", code)
            self.session.execute(exec_sql)
        self.session.commit()
        self.session.close()

    def DropAllTables(self, codes: list[str]) -> bool:
        self.session = sessionmaker(dbModel.Engine)()
        for code in codes:
            exec_sql = _drop_fund_table.format("qfq", code)
            self.session.execute(exec_sql)
        self.session.commit()
        self.session.close()

    def CheckTable(self, table: str, schema: str) -> bool:
        return dbModel.Engine.has_table(table_name=table, schema=schema)

    # def Insert(self, code:str, items: list[FundQfq]) -> bool:
    #     self.session = sessionmaker(dbModel.Engine)()
    #     for item in items:
    #         exec_sql=_insert_fund_table.format(code, item.date, item.open, item.close, item.hight, item.low,
    #     item.volume, item.volume_quota, item.vibration, item.rise_fall_rate, item.rise_fall_quota, item.turnover_rate)
    #         self.session.execute(exec_sql)
    #     self.session.commit()
    #     self.session.close()
    #     self.session = None
    #     return True

    def InsertList(self, code:str, items: list[FundQfq]) -> bool:
        self.session = sessionmaker(dbModel.Engine)()
        # for item in items:
        self.session.add_all(items)
        self.session.commit()
        self.session.close()
        self.session = None
        return True

    def Insert(self, code: str, item: FundQfq) -> bool:
        self.session = sessionmaker(dbModel.Engine)()
        # for item in items:
        self.session.add(item)
        self.session.commit()
        self.session.close()
        self.session = None
        return True

    def Merge(self, table: FundQfq, item: FundQfq) -> bool:
        self.session = sessionmaker(dbModel.Engine)()
        # for item in items:
        fund_obj = self.session.query(table).filter(table.date==item.date).one_or_none()
        if fund_obj != None:
            item.id = fund_obj.id
        self.session.merge(item)
        self.session.commit()
        self.session.close()
        self.session = None
        return True

    def QueryAll(self, code: str) -> tuple[pd.DataFrame, bool]:
        try:
            exec_sql = _select_fund_sql.format("qfq", code, 3000)
            df = pd.read_sql(exec_sql, dbModel.Engine)
            return df, True
        except Exception as result:
            logging.error("位置错误%s" % result)
            return pd.DataFrame(), False

    def QueryLastDay(self, table: FundQfq) -> FundQfq:
        self.session = sessionmaker(dbModel.Engine)()
        item = self.session.query(table).order_by(table.date.desc()).limit(1).one()
        self.session.close()
        self.session = None
        return item

    def Delete(self, code:str) -> bool :
        FundQfq.__tablename__ = "fund_{}_{}".format("qfq", code) 
        self.session = sessionmaker(dbModel.Engine)()
        self.session.query(FundQfq).filter(FundQfq.date!='1988-01-01').delete()
        self.session.commit()
        self.session.close()


g_fund_op = FundOperator()
g_market_fund_op = MarketFundOperator()